--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

DROP TABLE se_gen_societe;

CREATE TABLE se_gen_societe AS (SELECT * FROM &erp.gen_societe WHERE 1=2);






 INSERT INTO se_gen_societe

    (SELECT s.id soc_cod,
            '01' ets_cod,
            0 enreg_id,
            NULL delcal_cod,
            'N' enreg_s,
            adr.nom || ' ' || adr.cp  || ' ' || adr.bd || ' ' || adr.rue aff_nom  ,
            NULL debnivimp_num,
            NULL debnivexp_num,
            NULL debimpcontact_lib,
            NULL debexpcontact_lib,
            NULL debhabil_no,
            NULL debmdphabil_lib,
            NULL debsequence_num,
            'erp/logo/molinel.jpg' logo,
            NULL logoachat_lib,
            'N' etsref_ok,
            'O' achcde_ok,
            'O' achfac_ok,
            'N' achfacsoc_ok

        FROM UV_PSOCIETES s,UV_PSOCIETES_adrliv sadr,uv_padresses adr
        WHERE sadr.id=s.id AND adr.id=sadr.id||sadr.adrliv
          AND sadr.adrliv='01'

        ) ;

 UPDATE se_gen_societe tf SET (enreg_id)=(select enreg_id FROM &erp.gen_societe f WHERE f.soc_cod=tf.soc_cod AND f.ets_cod=tf.ets_cod) ;
 UPDATE se_gen_societe tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;


 CALL uvse_synchroverserp('soc_cod@ets_cod','GEN_SOCIETE','O') ;

 commit;

select * from (
SELECT &outils.stragg(source) source,soc_cod, ets_cod, enreg_id, delcal_cod, enreg_s, aff_nom, debnivimp_num, debnivexp_num, debimpcontact_lib, debexpcontact_lib, debhabil_no, debmdphabil_lib, debsequence_num, logo, logoachat_lib, etsref_ok, achcde_ok, achfac_ok, achfacsoc_ok
FROM (
  SELECT 'uv' source ,g.* FROM se_gen_societe g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.gen_societe g

  )
GROUP BY soc_cod, ets_cod, enreg_id, delcal_cod, enreg_s, aff_nom, debnivimp_num, debnivexp_num, debimpcontact_lib, debexpcontact_lib, debhabil_no, debmdphabil_lib, debsequence_num, logo, logoachat_lib, etsref_ok, achcde_ok, achfac_ok, achfacsoc_ok
HAVING Count(1)!=2) r
ORDER BY  soc_cod, ets_cod ;
